/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.NetworkTargetKey;
import com.google.api.adwords.starterkit.db.structures.NetworkTargetStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.starterkit.wrappers.CampaignWrapper;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;

/**
 * The Class NetworkTargetTable encapsulates most of the access to  
 * NetworkTarget table in DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class NetworkTargetTable {
  private PreparedStatement prepReplace;
  private PreparedStatement prepLoad;
  private PreparedStatement prepDel;
  private Connection conn;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public void initTable() {
    try { 
      Statement stat = conn.createStatement();      
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS NetworkTargets" 
        + "  (" 
        + "     LocalCampaignId INTEGER, "
        + "     CampaignId LONG, " 
        + "     Network STRING DEFAULT '', " 
        + "     PRIMARY KEY (LocalCampaignId, Network)" 
        + "  );");
    stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS NetworkTargetCampaignIdIndex " 
        + "ON NetworkTargets (CampaignId);");
      
      prepLoad = conn.prepareStatement(
          "SELECT * FROM NetworkTargets WHERE LocalCampaignId = ?;"); 

      prepReplace = conn.prepareStatement(
          "REPLACE INTO NetworkTargets (LocalCampaignId, CampaignId, Network)" 
          + " VALUES (?, ?, ?);");

      prepDel = conn.prepareStatement(
          "DELETE FROM NetworkTargets WHERE LocalCampaignId = ? ");              
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }        
  }

  /**
   * Instantiates a new platform utils.
   *
   * @param conn the DB connection
   */
  public NetworkTargetTable(Connection conn) {
    this.conn = conn;
    initTable();
  }

  /**
   * Convert the ResultSet format from DB into NetworkTargetStructure format 
   *
   * @param rs the ResultSet assigned to NetworkTarget table
   * @return the database row in NetworkTargetStructure format 
   */  
  public NetworkTargetStructure getResultSet(ResultSet rs) {
    NetworkTargetStructure networkTargetStructure 
        = new NetworkTargetStructure();
    try {
      networkTargetStructure.setLocalCampaignId(rs.getInt("LocalCampaignId"));
      networkTargetStructure.setCampaignId(rs.getLong("CampaignId"));
      networkTargetStructure.setNetwork(rs.getString("Network"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return networkTargetStructure;    
  }
  
  /**
   * Load a set of NetworkTarget from DB.
   *
   * @param networkTargetKey the key that specifies the local campaign id
   * @return a set of networkTarget assigned to the local campaign id
   */
  public HashSet<NetworkTargetStructure> get(
      NetworkTargetKey networkTargetKey) {
    HashSet<NetworkTargetStructure> langTargets 
        = new HashSet<NetworkTargetStructure>();
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, networkTargetKey.getLocalCampaignId());
      rs = prepLoad.executeQuery();
      while (rs.next()) {
        NetworkTargetStructure networkTargetStructure = getResultSet(rs);
        langTargets.add(networkTargetStructure);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
    return langTargets;
  }
  
  /**
   * Save a Network Target to DB
   *
   * @param networkTargets a set of Network Targets  
   */
  public void save(HashSet<NetworkTargetStructure> networkTargets) {        
    for (Iterator<NetworkTargetStructure> i 
        = networkTargets.iterator(); i.hasNext(); ) {
      NetworkTargetStructure networkTargetStructure = i.next();
      try {
        prepReplace.setInt(1, networkTargetStructure.getLocalCampaignId());
        prepReplace.setLong(2, networkTargetStructure.getCampaignId());
        prepReplace.setString(3, networkTargetStructure.getNetwork());
        prepReplace.executeUpdate();
      } catch (SQLException e) {
        DumpExceptions.dumpSQLException(e);
      }         
    }
  }
  
  /**
   * Delete a NetworkTarget from DB
   *
   * @param languageTargetKey the languageTarget to be deleted
   */
  public void del(NetworkTargetKey networkTargetKey) {
    try {
      prepDel.setLong(1, networkTargetKey.getLocalCampaignId());
      prepDel.execute();      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }       
  }  
  
  /**
   * Get all campaign ids from AdWords associated to campaigns just created 
   * by Starter Kit
   *
   * @return a map between campaign id and local campaign id 
   */
  public HashMap<Long, Integer> getNewCampaignsIds() {
    HashMap<Long, Integer> campaignsIds = new HashMap<Long, Integer>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.localId, ca.CampaignId FROM Campaigns ca, "
          + "NetworkTargets gt WHERE gt.LocalCampaignId = ca.LocalId AND "
          + "gt.CampaignId = 0;");
      while (rs.next()) {
        campaignsIds.put(rs.getLong("CampaignId"), rs.getInt("localId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
    return campaignsIds;
  }

  /**
   * Update campaigns ids at geo target table.
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId at NetworkTargets table");
    HashMap<Long, Integer> newCampaignsId = getNewCampaignsIds();
    PreparedStatement prep;
    try {
      prep = conn.prepareStatement(
          "UPDATE NetworkTargets SET CampaignId = ? WHERE LocalCampaignId = ?");
      int count = 0;
      for (long campaignId : newCampaignsId.keySet()) {
        int localCampaignId = newCampaignsId.get(campaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }    
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }      
  }
  
  /**
   * Return all NetworkTargets modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains NetworkTargets modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
            "SELECT lt.CampaignId CampaignId, lt.Network Network " 
          + "FROM NetworkTargets lt, Campaigns ca " 
          + "WHERE ca.IsNetworkModified = 1 " 
          + "AND ca.localId = lt.LocalCampaignId ORDER BY lt.CampaignId");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }
  
  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsNetworkModified = 0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }

  /**
   * Links a set of networkTargets to a campaign defined at CampaignWrapper
   *
   * @param campaignWrapper the campaignWrapper representing a campaign
   * @param networkTargets a set of networkTargets to be linked to the campaign
   */
  public static void link(CampaignWrapper campaignWrapper, 
      HashSet<NetworkTargetStructure> networkTargets) {
    for (Iterator<NetworkTargetStructure> i 
        = networkTargets.iterator(); i.hasNext(); ) {
      NetworkTargetStructure networkTargetStructure = i.next();
      networkTargetStructure.setCampaignId(campaignWrapper.getCampaignId());
      networkTargetStructure.setLocalCampaignId(campaignWrapper.getLocalId());
    }        
  }
  
  /**
   * Convert a set of NetworkTargetSettings structure into a set of Strings 
   * 
   * @param netTargets the original NetworkTargetSettings 
   */
  private static HashSet<String> convert(
      HashSet<NetworkTargetStructure> netTargets) {
    HashSet<String> nets = new HashSet<String>();
    for (Iterator<NetworkTargetStructure> i 
        = netTargets.iterator(); i.hasNext(); ) {
      NetworkTargetStructure networkTarget = i.next();
      nets.add(networkTarget.getNetwork().toLowerCase());
    }
    return nets;
  }
    
  /**
   * Verifies if two NetworkTargetList are equals
   *
   * @param a the GeoTargetList a
   * @param b the GeoTargetList b
   * @return true, if equals
   */
  public static boolean equals(HashSet<NetworkTargetStructure> a, 
      HashSet<NetworkTargetStructure> b) {
    HashSet<String> netA = convert(a);
    HashSet<String> netB = convert(b);
    
    if (netA.containsAll(netB) && netB.containsAll(netA)) {
      return true;
    }
    return false;
  }
}
